Generated code - Using the typed view classes, Adapter
Preface
LLBLGen Pro supports read-only lists based on a database view (Typed View) or a selection of entity fields from one or more
entities with a relation (not m:n) (Typed List). Although both elements are different, they both will be generated as a typed DataTable, which
is a variant of the typed DataSet concept included in Visual Studio.NET. A typed DataTable is a class which derives from the .NET DataTable
and defines properties and a row class to access the individual fields in a typed fashion.
In this section the typed view classes are briefly discussed and their usage is illustrated using examples.
Instantiating and using a Typed View
As described in the
concepts, a Typed View definition is a 1:1 mapping of a
database view on an element in an LLBLGen Pro project. A typed view contains, for each database view column, a field with the same
name or the name you gave it. When the typed view element is generated into code, it will end up as a class derived (indirectly) from DataTable, a typed
DataTable to be exact, which is usable as a read-only list. You can limit the number of rows using standard LLBLGen Pro filtering techniques.
These techniques use Predicate Expressions and relations included in a RelationPredicateBucket object.
(See:
Getting started with filtering).
Because the typed DataTable is derived from the .NET DataTable class, it can be used to create DataView objects. With DataView objects you can apply specify
additional filtering, sorting, calculations etc. As an illustration, we'll include the view 'Invoices' from the Northwind database, and use the Typed View
'Invoices' for the code examples.
Instantiating and filling a Typed View
To create an instance of the 'Invoices' typed view and fill it with all the data in the view, the following code is sufficient:
// [C#]
InvoicesTypedView invoices = new InvoicesTypedView();
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.FetchTypedView(invoices.GetFieldsInfo(), invoices);
' [VB.NET]
Dim invoices As New InvoicesTypedView()
Dim adapter As New DataAccessAdapter()
adapter.FetchTypedView(invoices.GetFieldsInfo(), invoices)
The rows will be added as they are received from the database provider; no sorting nor filtering will be applied. Furthermore, all rows in the
view are read, which is probably not what you want. Let's filter on the rows, so the Fill() method will only return those rows with an OrderID
larger than 11000, and do not filter out duplicate rows:
// [C#]
InvoicesTypedView invoices = new InvoicesTypedView();
DataAccessAdapter adapter = new DataAccessAdapter();
RelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.PredicateExpression.Add(InvoicesFields.OrderId > 11000);
adapter.FetchTypedView(invoices.GetFieldsInfo(), invoices, bucket, true);
' [VB.NET]
Dim invoices As New InvoicesTypedView()
Dim adapter As New DataAccessAdapter()
Dim bucket As New RelationPredicateBucket()
bucket.PredicateExpression.Add(New FieldCompareValuePredicate(InvoicesFields.OrderId, Nothing, ComparisonOperator.GreaterThan, 11000))
adapter.FetchTypedView(invoices.GetFieldsInfo(), invoices, bucket, True)
The overloaded FetchTypedView() versions also accept one or more of these other parameters:
- maxNumberOfItemsToReturn, which is used to limit the number of rows returned. When this parameter is set to 0, it is ignored (all rows
are returned)
- sortClauses, which is a collection of SortClause objects and is used to sort the rows in the table before they're added to the Typed View
object. If this parameter is set to null, no sorting is performed.
Specifying a filter will narrow down the number of rows to the ones matching the filter. The filter can be as complex as you want. See for
filtering information and how to set up sorting clauses
Getting started with filtering
and
Sorting.
Reading a value from a filled Typed View
After we've filled the typed view object, we can use the values read. As said, Typed View and Typed List objects in the form of a typed DataTable
are
read-only, and therefore extremely handy for filling lists on the screen or website, but not usable for data manipulation. For
modifying data you should use the entity classes/collection classes.
Below, we'll read a given value from row 0, the value for the Sales person.
We assume the invoices object is filled with data using any of the previously mentioned ways to do so.
// [C#]
string salesPerson = invoices[0].Salesperson;
' [VB.NET]
Dim salesPerson As String = invoices(0).Salesperson
That's it. The '0' points to the row, and the row is 'typed', thus has named properties for the individual columns in the
object; you can just read the value using a property.
Null values
Because the TypedView (and TypedList) classes are derived classes from DataTable, the underlying DataTable cells still contain System.DBNull.Value
values if the field in the database is NULL. You can test for NULL by using the generated methods Is
FieldNameNull(). When reading a field which value
is System.DBNull.Value in code, like the example above, will result in the default value for the type of the field, as defined in the TypeDefaultValue class.
Databinding will result in the usage of a DataView, as that's build into the DataTable, which will then return the System.DBNull.Value values and not the
TypeDefaultValue values.
Limiting and sorting a typed view
When sorting the data in a typed view we're not actually sorting the data in the object, but sorting the data before it is read into the object.
This is achieved by using a sort operator in the actual SQL query. To do this, you specify a set of SortClauses to the Fill() method. Below is an
example sorting the invoices typed view on the field 'ExtendedPrice' in descending order. Sort clauses are easily created using the SortClause
factory in the generated code. We pass the same filter as mentioned earlier and we assume the variable adapter still holds a reference to a
DataAccessAdapter instance.
// [C#]
invoices.Clear(); // clear al current data
ISortExpression sorterInvoices = new SortExpression(InvoicesFields.ExtendedPrice | SortOperator.Descending);
adapter.FetchTypedView(invoices.GetFieldsInfo(), invoices, bucket, 0, sorter, true);
' [VB.NET]
invoices.Clear() ' clear al current data
Dim sorterInvoices As ISortExpression = New SortExpression( _
New SortClause(InvoicesFields.ExtendedPrice, Nothing, SortOperator.Descending))
adapter.FetchTypedView(invoices.GetFieldsInfo(), invoices, bucket, 0, sorter, True)
The rows are now sorted on the ExtendedPrice field, in descending order.